# -*- coding: utf-8 -*-#
#-------------------------------------------------------------------------------
# 建立者:       王景渊  
# Name:         1461
# Description:
# Author:       dell
# Date:         2019/4/24
#-------------------------------------------------------------------------------


from sqlalchemy import create_engine,MetaData,Table,Column,Integer,String,Float,exc,orm
from sqlalchemy.ext.declarative import declarative_base

# 链接协议    数据库库包     用户名：密码@服务：端口/数据库名?字符集
mysql = 'mysql+pymysql://root:123@127.0.0.ajaxdata.json:3306/testdb?charset=utf8'
# 引擎链接数据
tablename = 'persons1'

engine = create_engine(mysql,encoding='utf-8')
engine.connect()

metadata = MetaData(engine)

person = Table(tablename,metadata,
               Column('id',Integer,primary_key= True),
               Column('name',String(30)),
               Column('age',Integer),
               Column('address',String(100)),
               Column('salary',Float))
metadata.create_all(engine)

Base = declarative_base()


# 数据表类 o实体类
class Person(Base):
    # 声明表名
    __tablename__ = 'persons1'

    id = Column('id',Integer,primary_key= True)#主键列
    name = Column('name',String(30))#varchar（10）
    age = Column('age',Integer)
    address = Column('address',String(100))
    salary = Column('salary',Float)#浮点类型

#创建 session
Session = orm.sessionmaker(bind=engine)
#实例化 session
session = Session()

#删除数据
session.query(Person).delete()
session.commit()

#实例化数据 对应的就是数据库表中的一行
person1 = Person(id=1,name='张三',age=30,address='长沙',salary=10000)
person2 = Person(id=2,name='李四',age=31,address='株洲',salary=9000)
person3 = Person(id=3,name='王五',age=31,address='湘潭',salary=8000)

session.add(person1)
session.add(person2)
session.add(person3)

#提交事务
session.commit()

#修改数据
p = session.query(Person).filter(Person.name =='李四').first()
p.address = '株洲市天元区'
session.add(p)
session.commit()

p = session.query(Person).filter(Person.name=='李四').first()
print(p.address)

ps = session.query(Person).all()

for p in ps:
    print(p.name)

session.close()


